﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;

namespace XNET.IO
{
    /// <summary>
    /// NPOI 操作EXCEL帮助类
    /// </summary>
    public class XExcel
    {
        private const string Excel2003 = ".xls";
        private const string Excel2007 = ".xlsx";
        /// <summary>
        /// NPOI Excel转换为DataTable
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">sheet名称,为空默认第一个Sheet</param>
        /// <param name="firstSheet">找不到Sheet时，是否返回第一个Sheet</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string fileName, string sheetName = "Sheet1", bool firstSheet = true)
        {
            //文件名为空或者Sheet名为空时直接返回
            if (string.IsNullOrEmpty(fileName)) return null;

            try
            {
                System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
                if (fileInfo.Exists)
                {
                    FileStream fs = fileInfo.OpenRead();//读取Excel文件
                    IWorkbook workbook = CreateInstance(fileInfo.Extension, fs, true); //创建一个Workbook
                    ISheet sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        //当按sheet名称查找失败时默认返回第一个Sheet
                        if (firstSheet)
                            sheet = workbook.GetSheetAt(0);
                        else
                            return null;
                    }
                    //返回第一行标题行
                    IRow firstRow = sheet.GetRow(0);

                    DataTable dt = new DataTable();
                    for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell == null) continue;
                        //添加列
                        DataColumn column = new DataColumn(cell.StringCellValue);
                        dt.Columns.Add(column);
                    }

                    int startRow = sheet.FirstRowNum + 1;
                    int lastRow = sheet.LastRowNum;

                    for (int i = startRow; i <= lastRow; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　

                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            ICell cell = row.GetCell(j);
                            //列为空时,设置值为空
                            if (cell == null)
                                dataRow[j] = null;
                            else
                                dataRow[j] = cell.ToString();
                        }

                        dt.Rows.Add(dataRow);
                    }

                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// NPOI DataTable转换为Excel
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static int DataTableToExcel(DataTable dt, string fileName, string sheetName = "Sheet1")
        {
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                IWorkbook workbook = CreateInstance(Path.GetExtension(fileName));
                if (workbook == null) return 0;

                ISheet sheet = workbook.CreateSheet(sheetName);

                //创建列
                IRow rowTitle = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    rowTitle.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                int count = 1;

                for (int i = 0; i < dt.Rows.Count; ++i)
                {
                    IRow row = sheet.CreateRow(count);
                    for (int j = 0; j < dt.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                    ++count;
                }
                workbook.Write(fs);//写入到excel

                return count; //返回转换行数
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据传入后缀名构建Workbook实例
        /// </summary>
        /// <param name="extension">文件扩展名</param>
        /// <param name="fs">读取文件流,写入时为空</param>
        /// <param name="ReadOrWrite">读取或者写入EXCEL</param>
        /// <returns>IWorkbook</returns>
        public static IWorkbook CreateInstance(string extension, FileStream fs = null, bool ReadOrWrite = false)
        {
            IWorkbook workbook = null;
            if (string.Compare(extension, Excel2003, true) == 0)
            {
                //office 2003版
                if (ReadOrWrite)
                    workbook = new HSSFWorkbook(fs);
                else
                    workbook = new HSSFWorkbook();

            }
            else if (string.Compare(extension, Excel2007, true) == 0)
            {
                //office 2007版本以上
                if (ReadOrWrite)
                    workbook = new XSSFWorkbook(fs);
                else
                    workbook = new XSSFWorkbook();
            }

            return workbook;
        }
    }
}
